package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.Grade;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.GradeCustomGrade;
import com.xmy.cultivate.entity.SatisfiedStatus;
import com.xmy.cultivate.excel.StudentGradeExcel;
import com.xmy.cultivate.respons.SchedulingGrade;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 班级 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2022-07-26
 */
@Mapper
public interface GradeMapper extends BaseMapper<Grade> {

    @Select("SELECT * FROM grade")
    @Results(
            {@Result(
                    column = "id",property = "id"
            ),
                    @Result(
                            property = "coursePricingList"
                            ,column = "id",
                            one = @One(select = "com.xmy.cultivate.mapper")
                    )
            })
    public abstract List<Map<String,Object>> getGradeList(Map<String,Object> map);

    @Select("select a.* from grade as a left join course as b on a.course_id = b.id ${ew.customSqlSegment}")
    @Results({
            @Result( column = "id",property = "id"),
            @Result(
                    property = "courseObj"
                    ,column = "course_id",
                    one=@One(select = "com.xmy.cultivate.mapper.CourseMapper.checkCourseForId")
            ),
            @Result(property = "teacherObj"
                    ,column = "teacher_id",
                    one=@One(select = "com.xmy.cultivate.mapper.StaffMapper.selectById")
            )
    })
    public abstract IPage<Grade> checkGrade(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select a.id as studentGradeId,b.id as gradeId,b.`name` as gradeName,b.course_id,getCourseNameForId(b.course_id),b.school_id,getSchoolName(b.school_id) as schoolName from student_grade as a left join grade as b on  a.student_id = b.id where a.status =1 and a.student_id= #{studentId}")
    public abstract List<Map<String,Object>> GetLessonByStuInfoId(Long studentId);


    @Select("SELECT * from grade_view ${ew.customSqlSegment}")
    @Results({
            @Result( column = "id",property = "id"),
            @Result(
                    property = "courseObj"
                    ,column = "course_id",
                    one=@One(select = "com.xmy.cultivate.mapper.CourseMapper.checkCourseForId")
            ),
            @Result(property = "teacherObj"
                    ,column = "teacher_id",
                    one=@One(select = "com.xmy.cultivate.mapper.StaffMapper.selectById")
            ),
            @Result(property = "schoolObj"
                    ,column = "school_id",
                    one=@One(select = "com.xmy.cultivate.mapper.OrganizationMapper.selectById")
            )
    })
    public abstract IPage<Grade> findAll(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("SELECT * from grade_view ${ew.customSqlSegment}")
    @Results({
            @Result( column = "id",property = "id"),
            @Result(
                    property = "courseObj"
                    ,column = "course_id",
                    one=@One(select = "com.xmy.cultivate.mapper.CourseMapper.checkCourseForId")
            ),
            @Result(property = "teacherObj"
                    ,column = "teacher_id",
                    one=@One(select = "com.xmy.cultivate.mapper.StaffMapper.selectById")
            ),
            @Result(property = "schoolObj"
                    ,column = "school_id",
                    one=@One(select = "com.xmy.cultivate.mapper.OrganizationMapper.selectById")
            )
    })
    public abstract IPage<Grade> selectAllotGrade(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("SELECT id from grade_view ${ew.customSqlSegment}")
    public abstract List<Long> findGradeIdList(@Param(Constants.WRAPPER) Wrapper wrapper);



    @Select("select a.* from grade as a where id = #{id}")
    @Results({
            @Result( column = "id",property = "id"),
            @Result(
                    property = "courseObj"
                    ,column = "course_id",
                    one=@One(select = "com.xmy.cultivate.mapper.CourseMapper.checkCourseForId")
            ),
            @Result(property = "teacherObj"
                    ,column = "teacher_id",
                    one=@One(select = "com.xmy.cultivate.mapper.StaffMapper.selectById")
            ),
            @Result(property = "schoolObj"
                    ,column = "school_id",
                    one=@One(select = "com.xmy.cultivate.mapper.OrganizationMapper.selectById")
            ),
            @Result(
                    property = "teacherList"
                    ,column = "id",
                    many=@Many(select = "com.xmy.cultivate.mapper.TypeValueDataMapper.selectListByForId")
            )
    })
    public abstract Grade getOneForId(@Param("id") Long id);

    @Select("select g.teacher_id,g.school_id,count(DISTINCT CASE WHEN evaluate_type = 1 THEN ps.id END) as satisfiedNumber ,count(DISTINCT CASE WHEN next_quarter_num = 3 THEN ps.id END) as summerNumber,count(DISTINCT ps.id) as allNumber from patriarch_satisfy as ps LEFT JOIN grade as g ON ps.grade_id = g.id ${ew.customSqlSegment}")
    public abstract List<SatisfiedStatus> getSatisfiedStatus(@Param(Constants.WRAPPER) QueryWrapper wrapper);

    @Select("select name from grade where id = #{gradeId}")
    public abstract String getGradeName(@Param("gradeId")Long gradeId);


    /**
     * 获取班级上课时间
     * @param schoolId
     * @param yearPart
     * @param qurarterNum
     * @return
     */
    @Select({"<script>",
            "select a.*,b.subjects_id,CONCAT(a.time_start, '-', a.time_end) AS school_time",
            "from grade as a left join course as b on a.course_id = b.id",
            "where 1 = 1",
            "and a.lesson_type = ${lessonType}",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and a.school_id = ${schoolId}",
            "</when>",
            "<when test='sourseLeve == 2'>",
            "and a.teacher_id = ${adminId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and b.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "and a.year_part = ${yearPart}",
            "</when>",
            "<when test='qurarterNum!=null and qurarterNum!=&apos;&apos;'>",
            "and a.quarter_num = ${qurarterNum}",
            "</when>",
            "<when test='weekDates!=null and weekDates!=&apos;&apos;'>",
            " and a.week_dates = #{weekDates}",
            "</when>",
            "and a.time_start is not null and a.time_end is not null and a.deleted=0  ORDER BY a.time_start asc ",
            "</script>"})
    public abstract List<SchedulingGrade> getGradeListForScheduling(@Param("schoolId") Long schoolId,@Param("subjectsId") Integer subjectsId, @Param("yearPart") Integer yearPart, @Param("qurarterNum") Integer qurarterNum,@Param("weekDates") String weekDates,@Param("lessonType") Integer lessonType,@Param("sourseLeve") Integer sourseLeve,@Param("adminId") Long adminId);

    /**
     * 获取班级上课时间
     * @param schoolId
     * @param yearPart
     * @param qurarterNum
     * @return
     */
    @Select({"<script>",
            "select CONCAT(a.time_start, '-', a.time_end) AS school_time",
            "from grade as a left join course as b on a.course_id = b.id",
            "where 1=1",
            "and a.lesson_type = ${lessonType},",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and a.school_id = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and b.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "and a.year_part = ${yearPart}",
            "</when>",
            "<when test='qurarterNum!=null and qurarterNum!=&apos;&apos;'>",
            "and a.quarter_num = ${qurarterNum}",
            "</when>",
            "<when test='weekDates!=null and weekDates!=&apos;&apos;'>",
            " and a.week_dates = #{weekDates}",
            "</when>",
            "and a.time_start is not null and a.time_end is not null and a.deleted=0 and b.deleted=0 ORDER BY a.time_start DESC LIMIT 1",
            "</script>"})
    public abstract String getGradeTime(@Param("schoolId") Long schoolId,@Param("subjectsId") Integer subjectsId, @Param("yearPart") Integer yearPart, @Param("qurarterNum") Integer qurarterNum,@Param("weekDates") String weekDates,@Param("lessonType") Integer lessonType);



    /**
     * 获取班级上课时间
     * @param schoolId
     * @param yearPart
     * @param qurarterNum
     * @return
     */
    @Select({"<script>",
            "select CONCAT(time_start, '-', time_end) AS school_time",
            "from grade",
            "where 1=1",
            "and lesson_type = ${lessonType}",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and school_id = ${schoolId}",
            "</when>",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "and year_part = ${yearPart}",
            "</when>",
            "<when test='qurarterNum!=null and qurarterNum!=&apos;&apos;'>",
            "and quarter_num = ${qurarterNum}",
            "</when>",
            "<when test='weekDates!=null and weekDates!=&apos;&apos;'>",
            " and week_dates = #{weekDates}",
            "</when>",
            "and time_start is not null and time_end is not null and deleted=0  GROUP BY time_start,time_end ORDER BY time_start asc ",
            "</script>"})
    public abstract List<String> getSchoolTimeList(@Param("schoolId") Long schoolId,@Param("yearPart") Integer yearPart,@Param("qurarterNum") Integer qurarterNum,@Param("weekDates") String weekDates,@Param("lessonType") Integer lessonType);

    @Select("select * from (select t1.* from grade as t1 LEFT JOIN course as t2 on t1.course_id = t2.id ) as t where deleted = 0 and year_part = ${yearPart} and quarter_num =${qurarterNum} and teacher_id = ${teacherId} and lesson_type =${lessonType} ORDER BY id desc limit 1")
    public abstract Grade getGradeOneForTeacherId(@Param("yearPart") Integer yearPart,@Param("qurarterNum") Integer qurarterNum,@Param("teacherId") Long teacherId,@Param("lessonType") Integer lessonType);

    /**
     * 新排班表
     */
    @Select({"<script>",
            "SELECT ",
            "gradeName,",
            "getSubjectsName ( subjectsIdNow ) AS subjectsNameNow,",
            "getSchoolName ( openClassSchoolId ) AS openClassSchoolName,",
            "getCourseTypeName(course_type_id)  AS classTypeName,",
            "studentName,",
            "linkman,",
            "linkman_phone,",
            "is_guanzhu,",
            "IFNULL( all_course_count / 3, 0 ) AS all_course_count,",
            "IFNULL( residue_course_count/ 3, 0 ) AS residue_course_count,",
            "IFNULL( use_course_count / 3, 0 ) AS use_course_count,",
            "IFNULL(arrive_count, 0 ) AS arrive_count,",
            "IFNULL(repair_count, 0 ) AS repair_count,",
            "getSubjectsName ( tt.subjects_id ) AS subjectsName,",
            "getSchoolName ( tt.school_id ) AS schoolName, ",
            "getStudentNameForId ( clan_student_id ) AS clan_student_name,",
            "getSchoolName ( clan_school_id ) AS clan_school_name,",
            "getSubjectsName ( clan_subjects_id ) AS clan_subject_name ",
            "FROM (",
            "SELECT ",
            "t.*,od.subjects_id,od.school_id,od.order_id,clan_student_id,clan_school_id,clan_subjects_id ",
            "FROM (",
            "SELECT ",
            "t1.school_id AS openClassSchoolId,",
            "t1.student_id,",
            "t4.`name` AS studentName,",
            "t4.linkman,",
            "t4.linkman_phone,",
            "t4.is_guanzhu,",
            "t1.subjects_id AS subjectsIdNow,",
            "t1.grade_id,",
            "t5.course_type_id,",
            "t5.`name` AS gradeName,",
            "t2.all_course_count,",
            "residue_course_count,",
            "use_course_count,",
            "arrive_count,",
            "repair_count ",
            "FROM ( ",
            "SELECT ",
            "school_id,student_id,subjects_id,year_class_id,grade_id,lesson_type,course_id,",
            "SUM( use_course_count ) AS use_course_count  ",
            "FROM ",
            "student_grade ",
            "WHERE ",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNumList} ",
            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
            "AND lesson_type = ${lessonType} ",
            "</when>",
            "AND deleted = 0 ",
            "GROUP BY ",
            "school_id,student_id,subjects_id,grade_id ",
            ") AS t1 ",
            "LEFT JOIN ( ",
            "SELECT ",
            "sum( all_course_count - out_house - roll_out_house ) AS all_course_count, ",
            "SUM( residue_course_count ) AS residue_course_count, ",
            "school_id,student_id,subjects_id ",
            "FROM ",
            "order_detail ",
            "WHERE ",
            "year_part = ${oldYearPart} ",
            "AND quarter_num = ${oldQuarterNum} ",
            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
            "AND lesson_type = ${lessonType} ",
            "</when>",
            "AND `status` &lt;&gt; 2 ",
            "AND deleted = 0 ",
            "GROUP BY school_id,student_id,subjects_id ",
            ") AS t2 ON t1.student_id = t2.student_id ",
            "AND t1.school_id = t2.school_id ",
            "AND t1.subjects_id = t2.subjects_id ",
            "LEFT JOIN ( ",
            "SELECT ",
            "IFNULL(SUM( CASE WHEN `status` = 1 AND is_deduct_house = 1 THEN buckle_class_house ELSE 0 END )/ 3, 0 ) AS arrive_count,",
            "IFNULL(sum( CASE WHEN `status` = 2 AND is_repair &lt;&gt; 1 AND is_deduct_money &lt;&gt; 1 THEN buckle_class_house ELSE 0 END )/ 3, 0 ) AS repair_count,",
            "school_id,student_id,subjects_id,old_grade_id ",
            "FROM ",
            "record_course ",
            "WHERE ",
            "year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNumList} ",
            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
            "AND lesson_type = ${lessonType} ",
            "</when>",
            "AND deleted = 0 ",
            "GROUP BY ",
            "school_id,subjects_id,student_id,old_grade_id ",
            ") AS t3 ON t1.student_id = t3.student_id ",
            "AND t1.school_id = t3.school_id ",
            "AND t1.subjects_id = t3.subjects_id ",
            "AND t1.grade_id = t3.old_grade_id ",
            "LEFT JOIN student AS t4 ON t4.id = t1.student_id ",
            "LEFT JOIN grade AS t5 ON t5.id = t1.grade_id ",
            "WHERE 1=1 ",
            "<when test='gradeIdList!=null and gradeIdList!=&apos;&apos;'>",
            "AND t1.grade_id IN(${gradeIdList}) ",
            "</when>",
            "<when test='schoolIdList!=null and schoolIdList!=&apos;&apos;'>",
            "AND t1.school_id IN(${schoolIdList}) ",
            "</when>",
            "<when test='yearClassIdList!=null and yearClassIdList!=&apos;&apos;'>",
            "AND t1.year_class_id IN(${yearClassIdList}) ",
            "</when>",
            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
            "AND t1.lesson_type = ${lessonType}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND t1.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='courseIdList!=null and courseIdList!=&apos;&apos;'>",
            "AND t1.course_id = ${courseIdList} ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
            "AND t5.teacher_id = ${teacherId}",
            "</when>",
            "<when test='status!=null and status!=&apos;&apos;'>",
            "AND t5.`status` = ${status}",
            "</when>",
            "<when test='name!=null and name!=&apos;&apos;'>",
            "AND t5.`name` LIKE '%${name}%' ",
            "</when>",
            ") AS t ",
            "LEFT JOIN order_detail od ON t.student_id = od.student_id ",
            "LEFT JOIN ( ",
            "SELECT ",
            "t1.student_id AS clan_student_id,t1.order_id,t3.subjects_id AS clan_subjects_id,t3.school_id AS clan_school_id,t2.student_id ",
            "FROM ",
            "order_dis_clan AS t1 ",
            "INNER JOIN order_apply AS t2 ON t1.order_id = t2.id ",
            "LEFT JOIN student_grade AS t3 ON t3.student_id = t1.student_id ",
            "WHERE ",
            "t3.year_part = ${yearPart} ",
            "AND t3.quarter_num = ${quarterNumList} ",
            "AND t3.lesson_type = 1 ",
            "AND t3.is_last = 1 ",
            "AND t3.deleted = 0 ",
            "GROUP BY t3.student_id,t3.subjects_id ",
            ") AS odc ON odc.student_id = t.student_id ",
            "WHERE ",
            "1 = 1 ",
            "AND od.year_part = ${yearPart} ",
            "AND od.quarter_num = ${quarterNumList} ",
//            "AND od.student_id IN ( ",
//            "SELECT student_id FROM order_apply ",
//            "WHERE deleted = 0 AND `status` &lt;&gt; 2 ",
//            "AND year_part = ${yearPart} ",
//            "AND quarter_num = ${quarterNumList} ",
//            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
//            "AND lesson_type = ${lessonType} ",
//            "</when>",
//            ") ",
            "AND od.student_id IN ( ",
            "SELECT student_id FROM student_grade ",
            "WHERE deleted = 0 AND `status` = 1 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNumList} ",
            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
            "AND lesson_type = ${lessonType} ",
            "</when>",
            "AND order_detail_id = od.id ",
            ") ",
            "GROUP BY t.student_id,od.subjects_id,clan_subjects_id ",
            "ORDER BY t.grade_id,t.student_id,od.subjects_id ",
            ") AS tt ",
            "</script>"})
    public abstract List<StudentGradeExcel> workingScheduleNew(@Param("schoolIdList") String schoolIdList, @Param("name") String name, @Param("yearClassIdList") String yearClassIdList, @Param("subjectsId") String subjectsId, @Param("status") String status, @Param("classRoomIdList") String classRoomIdList, @Param("courseIdList") String courseIdList, @Param("yearPart") String yearPart,@Param("quarterNumList") String quarterNumList,@Param("teacherId") String teacherId,@Param("lessonType") String lessonType,@Param("gradeIdList") String gradeIdList,@Param("oldYearPart") Integer oldYearPart,@Param("oldQuarterNum") Integer oldQuarterNum);

    /**
     * 新排班表（预分班）
     */
    @Select({"<script>",
            "SELECT ",
            "t1.grade_name,",
            "t1.school_id,",
            "getSchoolName(t1.school_id) AS openClassSchoolName,",
            "t1.course_type_id,",
            "getCourseTypeName(t1.course_type_id) AS classTypeName,",
            "t1.student_name,",
            "t3.linkman,",
            "t3.linkman_phone,",
            "t1.subjects_id,",
            "t2.pass_shut_num,",
            "t2.right_rade,",
            "t2.midterm_score ",
            "FROM ",
            "student_grade_view AS t1 ",
            "LEFT JOIN `student_class_type` AS t2 ON t1.student_id = t2.student_id ",
            "AND t1.subjects_id = t2.subjects_id ",
            "AND t2.year_part = ${oldYearPart} ",
            "AND t2.quarter_num = ${oldQuarterNum} ",
            "LEFT JOIN student AS t3 ON t1.student_id = t3.id ",
            "WHERE ",
            "t1.quarter_num = ${quarterNumList} ",
            "AND t1.year_part = ${yearPart} ",
            "<when test='schoolIdList!=null and schoolIdList!=&apos;&apos;'>",
            "AND t1.school_id IN (${schoolIdList}) ",
            "</when>",
            "<when test='yearClassIdList!=null and yearClassIdList!=&apos;&apos;'>",
            "AND t1.year_class_id IN (${yearClassIdList}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND t1.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
            "AND t1.teacher_id = ${teacherId}",
            "</when>",
            "<when test='name!=null and name!=&apos;&apos;'>",
            "AND t1.grade_name LIKE '%${name}%' ",
            "</when>",
            "<when test='gradeIdList!=null and gradeIdList!=&apos;&apos;'>",
            "AND t1.grade_id IN (${gradeIdList}) ",
            "</when>",
            "AND t1.reading_status IN ( 0, 1 ) ",
            "AND t1.lesson_type = ${lessonType} ",
            "AND t1.deleted = 0 ",
            "GROUP BY",
            "t1.student_id,t1.grade_id ",
            "ORDER BY t1.subjects_id,t1.teacher_id,t1.year_class_id,t1.grade_id ",
            "</script>"})
    public abstract List<StudentGradeExcel> workingScheduleBeforehandNew(@Param("schoolIdList") String schoolIdList, @Param("name") String name, @Param("yearClassIdList") String yearClassIdList, @Param("subjectsId") String subjectsId, @Param("status") String status, @Param("classRoomIdList") String classRoomIdList, @Param("courseIdList") String courseIdList, @Param("yearPart") String yearPart, @Param("quarterNumList") String quarterNumList, @Param("teacherId") String teacherId, @Param("lessonType") String lessonType, @Param("gradeIdList") String gradeIdList,@Param("oldYearPart") Integer oldYearPart,@Param("oldQuarterNum") Integer oldQuarterNum);


    @Select("SELECT * FROM (SELECT grade.*,COUNT( student_grade.student_id ) AS student_count,getStaffNameForId(grade.teacher_id) AS teacherName,getClassNameForId(grade.course_type_id) AS className,getSubjectsName(grade.subjects_id) AS subjectsName,getSchoolName(grade.school_id) AS schoolName FROM grade LEFT JOIN student_grade ON grade.id = student_grade.grade_id AND student_grade.use_course_count >= 0 AND student_grade.reading_status IN ( 0, 1 ) AND student_grade.deleted = 0 ${ew.customSqlSegment} ) AS t")
    public abstract IPage<Grade> getGradePosition(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     *
     * @param yearPart
     * @param quarterNum
     * @param score
     * @param classTypeId
     * @param type 1按分数，2按班型
     * @return
     */
    @Select({"<script>",
            "SELECT",
                    "* ",
                    "FROM",
                    "(",
                    "SELECT",
                      "*,",
                      "(",
                      "SELECT",
                        "count(*) ",
                      "FROM",
                        "student_grade AS sg ",
                      "WHERE",
                        "sg.grade_id = grade.id ",
                        "AND sg.deleted = 0 ",
                        "AND (",
                        "sg.reading_status IN ( 1, 4, 5 ) ",
                        "OR ( sg.reading_status IN ( 2, 3 ) AND sg.use_course_count > 0 )) ",
                        "AND sg.is_last = 1 ",
                      ") AS grade_people_count ",
                    "FROM",
                      "grade ",
                    "WHERE",
                      "deleted = 0 ",
                      "AND year_part = ${yearPart} ",
                      "AND quarter_num = ${quarterNum} ",
                      "AND school_id = ${schoolId} ",
                      "AND subjects_id = ${subjectsId} ",
                      "AND year_class_id = ${yearClassId} ",
                      "AND lesson_type = ${lessonType} ",
                      "AND `status` = 1 ",
                      "AND `position_num` &gt; 0 ",
                    //"<when test='type == 1'>",
                    //  "AND ${score} BETWEEN start_score AND end_score ",
                    //"</when>",
                    "<when test='type == 2'>",
                    "AND course_type_id = ${classTypeId} ",
                    "</when>",
                    "ORDER BY",
                      "position_num ASC ",
                    ") AS t1 ",
                    "WHERE",
                    "full_count > grade_people_count",
                    "ORDER BY position_num ASC",
            "</script>"})
    public abstract List<Grade> getPositionGradeList(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum,@Param("schoolId") Long schoolId,@Param("subjectsId") Long subjectsId,@Param("yearClassId") Integer yearClassId, @Param("lessonType") Integer lessonType, @Param("type") Integer type, @Param("score") BigDecimal score, @Param("classTypeId") Long classTypeId);

    /**
     * 浮动排班列表
     */
    @Select({"<script>",
            "SELECT *,GREATEST( SUM( full_count - student_count), 0 ) AS residue_count ",
            "FROM ( ",
            "SELECT ",
            "t1.*,",
            "getStaffNameForId ( t1.teacher_id ) AS teacher_name,",
            "getSchoolName ( t1.school_id ) AS school_name,",
            "getCourseTypeName(t1.course_type_id) AS class_type_name,",
            "getClassNameForId ( t1.class_room_id ) AS class_room_name,",
            "SUM( CASE WHEN t2.id IS NOT NULL THEN 1 ELSE 0 END ) AS student_count,",
            "SUM( CASE WHEN t2.id IS NOT NULL AND t3.id IS NOT NULL THEN 1 ELSE 0 END ) AS new_student_count,",
            "SUM( CASE WHEN t2.id IS NOT NULL AND t3.id IS NULL THEN 1 ELSE 0 END ) AS old_student_count  ",
            "FROM grade AS t1 ",
            "LEFT JOIN student_grade AS t2 ON t1.id = t2.grade_id ",
            "AND (t2.reading_status IN ( 0, 1 ) OR ( t2.use_course_count &gt; 0 )) ",
            "AND t2.deleted = 0 ",
            "AND t2.`status` &lt;&gt; 3 ",
            "LEFT JOIN ( ",
            "SELECT ",
            "t01.id,t01.student_id,t02.year_part,t02.quarter_num,t02.subjects_id_str ",
            "FROM ",
            "enroll_info AS t01 ",
            "INNER JOIN order_apply AS t02 ON t01.order_id = t02.id ",
            "WHERE ",
            "t02.`status` &lt;&gt; 2 ",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "AND t02.year_part = ${yearPart} ",
            "</when>",
            "<when test='quarterNum!=null and quarterNum!=&apos;&apos;'>",
            "AND t02.quarter_num in (${quarterNum}) ",
            "</when>",
            "GROUP BY t02.id ",
            ") AS t3 ON t3.year_part = t1.year_part ",
            "AND t3.quarter_num = t1.quarter_num ",
            "AND t3.student_id = t2.student_id ",
            "AND t3.subjects_id_str = t1.subjects_id ",
            "WHERE t1.lesson_type = 1 AND t2.is_last = 1 ",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "AND t1.year_part = ${yearPart} ",
            "</when>",
            "<when test='quarterNum!=null and quarterNum!=&apos;&apos;'>",
            "AND t1.quarter_num = ${quarterNum} ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "AND t1.year_class_id in (${yearClassId}) ",
            "</when>",
            "<when test='courseTypeId!=null and courseTypeId!=&apos;&apos;'>",
            "AND t1.course_type_id in (${courseTypeId}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND t1.subjects_id in (${subjectsId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND t1.school_id in (${schoolId}) ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
            "AND t1.teacher_id in (${teacherId}) ",
            "</when>",
            "<when test='classRoomId!=null and classRoomId!=&apos;&apos;'>",
            "AND t1.class_room_id in (${classRoomId}) ",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
            "AND t1.id in (${gradeId}) ",
            "</when>",
            "<when test='iSeal!=null and iSeal!=&apos;&apos;'>",
            "AND t1.is_seal = ${iSeal} ",
            "</when>",
            "<when test='startDate!=null and startDate!=&apos;&apos;'>",
            "AND t2.created_at &gt;= '${startDate}' ",
            "</when>",
            "<when test='endDate!=null and endDate!=&apos;&apos;'>",
            "AND t2.created_at &lt;= '${endDate}' ",
            "</when>",
            "GROUP BY t1.id ",
            ") AS t ",
            "GROUP BY id ",
            "ORDER BY ",
            "<when test='isDescOrAsc!=null and isDescOrAsc!=&apos;&apos; and isFieldToSc!=null and isFieldToSc!=&apos;&apos;'>",
                "<when test='isDescOrAsc == 0'>",
                    " ${isFieldToSc} ASC, ",
                "</when>",
                "<when test='isDescOrAsc == 1'>",
                    "${isFieldToSc} DESC, ",
                "</when>",
            "</when>",
            "id ASC ",
            "</script>"})
    public abstract IPage<Grade> getGradeFloatList(IPage<?> page, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("yearClassId") String yearClassId, @Param("courseTypeId") String courseTypeId, @Param("subjectsId") String subjectsId, @Param("schoolId") String schoolId, @Param("teacherId") String teacherId, @Param("classRoomId") String classRoomId, @Param("gradeId") String gradeId, @Param("isDescOrAsc") String isDescOrAsc, @Param("isFieldToSc") String isFieldToSc,@Param("iSeal") String iSeal,@Param("startDate") String startDate,@Param("endDate") String endDate);


    /**
     * 排班学账号导出
     */
    @Select({"<script>",
            "SELECT ",
            "t5.`name` AS gradeName,",
            "getSchoolName ( t1.school_id ) AS open_class_school_name,",
            "t1.student_id,",
            "t4.`name` AS studentName,",
            "t1.grade_id,",
            "getCourseTypeName ( t5.course_type_id ) AS class_type_name,",
            "t3.account,t3.pwd ",
            "FROM ( ",
            "SELECT ",
            "school_id,student_id,subjects_id,year_class_id,grade_id,lesson_type,course_id ",
            "FROM ",
            "student_grade ",
            "WHERE ",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNumList} ",
            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
            "AND lesson_type = ${lessonType} ",
            "</when>",
            "AND deleted = 0 ",
            "AND `status` = 1 ",
            "GROUP BY ",
            "school_id,student_id,subjects_id,grade_id ",
            ") AS t1 ",
            "LEFT JOIN student_account AS t3 ON t3.student_id = t1.student_id ",
            "LEFT JOIN student AS t4 ON t4.id = t1.student_id ",
            "LEFT JOIN grade AS t5 ON t5.id = t1.grade_id ",
            "WHERE 1=1 ",
            "<when test='gradeIdList!=null and gradeIdList!=&apos;&apos;'>",
            "AND t1.grade_id IN(${gradeIdList}) ",
            "</when>",
            "<when test='schoolIdList!=null and schoolIdList!=&apos;&apos;'>",
            "AND t1.school_id IN(${schoolIdList}) ",
            "</when>",
            "<when test='yearClassIdList!=null and yearClassIdList!=&apos;&apos;'>",
            "AND t1.year_class_id IN(${yearClassIdList}) ",
            "</when>",
            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
            "AND t1.lesson_type = ${lessonType}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND t1.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='courseIdList!=null and courseIdList!=&apos;&apos;'>",
            "AND t1.course_id = ${courseIdList} ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
            "AND t5.teacher_id = ${teacherId}",
            "</when>",
            "<when test='status!=null and status!=&apos;&apos;'>",
            "AND t5.`status` = ${status}",
            "</when>",
            "<when test='name!=null and name!=&apos;&apos;'>",
            "AND t5.`name` LIKE '%${name}%' ",
            "</when>",
            "ORDER BY t5.id,t4.id ",
            "</script>"})
    public abstract List<StudentGradeExcel> workingScheduleAccount(@Param("schoolIdList") String schoolIdList, @Param("name") String name, @Param("yearClassIdList") String yearClassIdList, @Param("subjectsId") String subjectsId, @Param("status") String status, @Param("classRoomIdList") String classRoomIdList, @Param("courseIdList") String courseIdList, @Param("yearPart") String yearPart,@Param("quarterNumList") String quarterNumList,@Param("teacherId") String teacherId,@Param("lessonType") String lessonType,@Param("gradeIdList") String gradeIdList,@Param("oldYearPart") Integer oldYearPart,@Param("oldQuarterNum") Integer oldQuarterNum);

    /**
     * 班级列表中的课程表（单科）
     *
     * @param page
     * @param gradeId
     * @return
     */
    @Select({"<script>",
            "SELECT t.*,",
            "student_at_school.`name` AS reading_school_name,",
            "student.`name` AS student_name,",
            "GROUP_CONCAT( time_start_end ORDER BY t.week_dates_02 ) AS school_time",
            "FROM ( ",
            "SELECT ",
            "t01.*, ",
            "t02.time_start AS time_start_02, ",
            "t02.time_end AS time_end_02, ",
            "t02.week_dates AS week_dates_02,",
            "t02.time_start_end",
            "FROM ",
            "( ",
            "SELECT ",
            "t1.id, ",
            "t1.grade_id, ",
            "t1.year_part, ",
            "t1.quarter_num, ",
            "t1.student_id, ",
            "t1.subjects_id, ",
            "t2.week_dates,",
            "CONCAT(getWeekdayName(t2.week_dates),'', t2.time_start) AS time_start,",
            "t2.time_end  ",
            "FROM ",
            "student_grade AS t1 ",
            "INNER JOIN grade AS t2 ON t1.grade_id = t2.id  ",
            "WHERE ",
            "t1.year_part = ${yearPart}  ",
            "AND t1.quarter_num = ${quarterNum}  ",
            "<if test= 'lessonType == 1'>",
                "AND t1.grade_id = ${gradeId} ",
            "</if>",
            "AND t1.subjects_id = ${subjectsId} ",
            "AND t1.lesson_type = 1  ",
            "AND t1.deleted = 0  ",
            "AND t1.`status` &lt;&gt; 2  ",
            "<if test= 'status == 1'>",
            "AND t1.reading_status IN ( 0, 1 ) ",
            "</if>",
            "GROUP BY student_id ",
            ") AS t01 ",
            "<if test= 'lessonType == 1'>",
                "LEFT ",
            "</if>",
            "<if test= 'lessonType != 1'>",
            "RIGHT ",
            "</if>",
            "JOIN ( ",
            "SELECT ",
            "t1.id, ",
            "t1.year_part, ",
            "t1.quarter_num, ",
            "t1.student_id, ",
            "t1.subjects_id, ",
            "t2.time_start, ",
            "t2.time_end, ",
            "t2.week_dates, ",
            "CONCAT( getWeekdayName(t2.week_dates) ,'', t2.time_start, ' - ', t2.time_end ) AS time_start_end",
            "FROM ",
            "student_grade AS t1 ",
            "INNER JOIN grade AS t2 ON t1.grade_id = t2.id  ",
            "WHERE ",
            "t1.year_part = ${yearPart}  ",
            "AND t1.quarter_num = ${quarterNum}  ",
            "<if test= 'lessonType != 1'>",
                "AND t1.grade_id = ${gradeId} ",
            "</if>",
            "AND t1.lesson_type &lt;&gt; 1  ",
            "AND t1.deleted = 0  ",
            "AND t1.`status` &lt;&gt; 2  ",
            "AND t1.reading_status IN ( 0, 1 )) AS t02 ON t01.year_part = t02.year_part  ",
            "AND t01.quarter_num = t02.quarter_num  ",
            "AND t01.student_id = t02.student_id  ",
            "AND t01.subjects_id = t02.subjects_id  ",
            ") AS t  ",
            "LEFT JOIN student ON student.id = student_id ",
            "LEFT JOIN student_at_school ON student.reading_school_id = student_at_school.id ",
            "WHERE 1=1 ",
            "<when test='studentName!=null and studentName!=&apos;&apos;'>",
            "AND student.`name` LIKE '%${studentName}%' ",
            "</when>",
            "GROUP BY ",
            "t.grade_id, ",
            "student_id, ",
            "subjects_id",
            "</script>"})
    public abstract IPage<Grade> timetableByGradeId(IPage<?> page, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("gradeId") String gradeId, @Param("subjectsId") String subjectsId, @Param("status") Integer status, @Param("studentName") String studentName, @Param("lessonType") Integer lessonType);

    /**
     * 班级列表中的课程表（多科）
     *
     * @param gradeId
     * @return
     */
   /* @Select({"<script>",
            "SELECT ",
            "t1.student_id,",
            "t3.`name` AS student_name,",
            "t2.`name` AS `name`,",
            "t2.week_dates,",
            "t2.time_start,",
            "t2.time_end,",
            "t2.subjects_id,",
            "t2.lesson_type ",
            "FROM ",
            "student_grade AS t1",
            "INNER JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "LEFT JOIN student AS t3 ON t1.student_id = t3.id ",
            "WHERE ",
            "t1.student_id IN (  ",
            "SELECT student_id FROM student_grade WHERE grade_id IN( ${gradeId} ) AND reading_status IN ( 0, 1 ) ",
            ") ",
            "AND t1.reading_status IN ( 0, 1 ) ",
            "AND t1.`status` &lt;&gt; 2 ",
            "AND t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.deleted = 0 ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                "AND t2.subjects_id = ${subjectsId} ",
            "</when>",
            "GROUP BY ",
            "t1.student_id, ",
            "t1.grade_id ",
            "ORDER BY ",
            "t1.student_id, ",
            "t2.subjects_id,",
            "t2.lesson_type ",
            "</script>"})*/
    @Select({"<script>",
            "SELECT",
            "t1.student_id,",
            "t1.student_name,",
            "t1.grade_name AS `name`,",
            "t1.week_dates,",
            "t1.time_start,",
            "t1.time_end,",
            "t1.subjects_id,",
            "t1.lesson_type",
            "FROM",
            "student_grade_view AS t1",
            "LEFT JOIN student_grade_view AS t2 ON t1.student_id = t2.student_id",
            "AND t1.year_part = t2.year_part",
            "AND t1.quarter_num = t2.quarter_num",
            "AND t1.reading_status = t2.reading_status",
            "WHERE",
            "t1.reading_status IN ( 0, 1 )",

            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
                "AND t2.grade_id IN (${gradeId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                "AND t2.school_id IN (${schoolIdList}) ",
            "</when>",
            "<when test='yearClassIdList!=null and yearClassIdList!=&apos;&apos;'>",
                "AND t2.year_class_id IN (${yearClassIdList}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                "AND t2.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='courseIdList!=null and courseIdList!=&apos;&apos;'>",
                "AND t2.course_id = ${courseIdList} ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
                "AND t2.teacher_id = ${teacherId}",
            "</when>",
            "<when test='name!=null and name!=&apos;&apos;'>",
                "AND t2.`name` LIKE '%${name}%' ",
            "</when>",

            "AND t1.`status` &lt;&gt; 2",
            "AND t1.year_part = ${yearPart}",
            "AND t1.quarter_num = ${quarterNum}",
            "AND t1.deleted = 0",
            "GROUP BY",
            "t1.student_id,",
            "t1.grade_id",
            "ORDER BY",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.lesson_type,",
            "t1.week_dates",
            "</script>"})
    public abstract IPage<Grade> timetableByGradeIdList(IPage<?> page, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("gradeId") String gradeId, @Param("subjectsId") String subjectsId, @Param("schoolId") String schoolId, @Param("classRoomIdList") String classRoomIdList, @Param("courseIdList") String courseIdList, @Param("yearClassIdList") String yearClassIdList, @Param("name") String name, @Param("teacherId") String teacherId);


    @Select("select subjects_id from grade where year_part = ${yearPart} and quarter_num = ${quarterNum} and school_id =${schoolId} and `status`  =1 and teacher_id = ${teacherId} and deleted = 0 LIMIT 1")
    public abstract Long getSubjectsForTeacherId(@Param("yearPart") Integer yearPart,
                                                 @Param("quarterNum") Integer quarterNum,
                                                 @Param("schoolId") Long schoolId,
                                                 @Param("teacherId") Long teacherId
                                                 );




  /*  @Update({
            "<script>",
            "UPDATE grade",
            "SET deleted = 1",
            "WHERE",
            "    (",
            "        <foreach collection='timeConditions' item='condition' separator=' AND '>",
            "            (time_start &lt;&gt; #{condition.timeStart} AND time_end &lt;&gt; #{condition.timeEnd}) ",
            "            <choose>",
            "                <when test='condition.schedulingType == \"A类\"'>",
            "                    AND week_dates IN (1, 2, 3, 4, 5)",
            "                </when>",
            "                <when test='condition.schedulingType == \"B类\"'>",
            "                    AND week_dates IN (0, 6)",
            "                </when>",
            "            </choose>",
            "            AND scheduling_type = #{condition.schedulingType}",
            "        </foreach>",
            "    )",
            "    AND year_part = ${yearPart}",
            "    AND quarter_num = ${quarterNum}",
            "    AND subjects_id = ${subjectsId}",
            "    AND lesson_type = ${lessonType}",
            "    AND school_id = ${schoolId}",
            "    AND add_type = 2",
            "    AND deleted = 0",
            "</script>"
    })
    boolean deleteGrade(
            @Param("timeConditions") List<GradeCustomGrade> timeConditions,
            @Param("yearPart") Integer yearPart,
            @Param("quarterNum") Integer quarterNum,
            @Param("subjectsId") Long subjectsId,
            @Param("lessonType") Integer lessonType,
            @Param("schoolId") Long schoolId);*/


    @Update({
            "<script>",
            "UPDATE grade",
            "SET deleted = 1",
            "WHERE",
            "    year_part = ${yearPart}",
            "    AND quarter_num = ${quarterNum}",
            "    AND subjects_id = ${subjectsId}",
            "    AND lesson_type = ${lessonType}",
            "    AND school_id = ${schoolId}",
            "    AND add_type = 2",
            "    AND deleted = 0",
            "    AND id NOT IN (",
            "        SELECT id",
            "        FROM (",
            "            SELECT id",
            "            FROM grade",
            "where",
            "    (",
            "        <foreach collection='timeConditions' item='condition' separator=' OR '>",
            " (" ,
            "time_start = #{condition.timeStart} AND time_end =#{condition.timeEnd}",
            "            <choose>",
            "                <when test='condition.schedulingType == \"A类\"'>",
            "                    AND week_dates IN (1, 2, 3, 4, 5)",
            "                </when>",
            "                <when test='condition.schedulingType == \"B类\"'>",
            "                    AND week_dates IN (0, 6)",
            "                </when>",
            "            </choose>",
            "            AND scheduling_type = #{condition.schedulingType}",
            ") ",
            "        </foreach>",
            "    )",
            "                AND year_part = ${yearPart}",
            "                AND quarter_num = ${quarterNum}",
            "                AND subjects_id = ${subjectsId}",
            "                AND lesson_type = ${lessonType}",
            "                AND school_id = ${schoolId}",
            "                AND add_type = 2",
            "                AND deleted = 0",
            "        ) AS t1",
            "    )",
            "</script>"
    })
    boolean deleteGrade(
            @Param("timeConditions") List<GradeCustomGrade> timeConditions,
            @Param("yearPart") Integer yearPart,
            @Param("quarterNum") Integer quarterNum,
            @Param("subjectsId") Long subjectsId,
            @Param("lessonType") Integer lessonType,
            @Param("schoolId") Long schoolId);

}
